/****** Object:  StoredProcedure [dbo].[sp_UpdateFinishedProduct]    Script Date: 06/09/2011 10:00:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateFinishedProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateFinishedProduct]
GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateFinishedProduct]    Script Date: 06/09/2011 10:00:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_UpdateFinishedProduct]
	@orderMaterialIdList				NVARCHAR(50),
	@orderMaterialFinishedQuantity		NVARCHAR(50),
	@orderId							INT
AS
BEGIN
	
	SET NOCOUNT ON;
	
	DECLARE @orderMaterialIdIndex					INT = 0,
			@orderMaterialFinishedQuantityIndex		INT = 0,
			@orderMaterialId						NVARCHAR(10),
			@orderMaterialValue						INT,
			@delimiter								NVARCHAR(2),
			@finishedQuantity						INT = 0

	SET @delimiter = '#'
	/*SET @orderMaterialIdList = @orderMaterialIdList + @delimiter*/
	SET @orderMaterialIdIndex = CHARINDEX(@delimiter,@orderMaterialIdList)
	SET @orderMaterialFinishedQuantityIndex = CHARINDEX(@delimiter,@orderMaterialFinishedQuantity)

	WHILE (@orderMaterialFinishedQuantityIndex <> 0)
	BEGIN
		SET @orderMaterialId = SUBSTRING(@orderMaterialIdList,1,@orderMaterialIdIndex - 1)
		SET @orderMaterialValue = CONVERT(INT,SUBSTRING(@orderMaterialFinishedQuantity,1,@orderMaterialFinishedQuantityIndex - 1))
				
		UPDATE [OrderMaterial]
		SET [finishedQuantity] = @orderMaterialValue
		WHERE [orderId] = @orderId
			  AND [materialId] = @orderMaterialId
		
		SET @finishedQuantity = @finishedQuantity + @orderMaterialValue
		
		SET @orderMaterialIdList = SUBSTRING(@orderMaterialIdList,@orderMaterialIdIndex+1,LEN(@orderMaterialIdList))
		SET @orderMaterialFinishedQuantity = SUBSTRING(@orderMaterialFinishedQuantity,@orderMaterialFinishedQuantityIndex+1,LEN(@orderMaterialFinishedQuantity))
		
		SET @orderMaterialIdIndex = CHARINDEX(@delimiter,@orderMaterialIdList)
		SET @orderMaterialFinishedQuantityIndex = CHARINDEX(@delimiter,@orderMaterialFinishedQuantity)
		
	END   
	
	DECLARE @orderQuantity	INT
	
	SELECT @orderQuantity = SUM([orderQuantity]) FROM [OrderMaterial] WHERE [orderId] = @orderId
	
	IF(@finishedQuantity = @orderQuantity)
	BEGIN
		UPDATE [Order]
		SET [isFinished] = 1
		WHERE [id] = @orderId
	END
	
END

GO


